﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;

namespace Hisun.Controllers
{
    /// <summary>
    /// Copyright (C) 2008 HOHO``
    /// All rights reserved
    /// NameSpace:Hisun.Controllers.ExcelImport
    /// Description :
    /// Created By QiuZhongJie At 2008/10/15 19:43
    /// </summary>
    public class ExcelImport
    {
        protected string lastError;


        /// <summary>
        /// Gets the last error.
        /// </summary>
        /// <value>The last error.</value>
        public string LastError
        {
            get { return lastError; }
        }

        protected Exception lastException;

        /// <summary>
        /// Gets the last exception.
        /// </summary>
        /// <value>The last exception.</value>
        public Exception LastException
        {
            get { return lastException; }
        }

        /// <summary>
        /// Imports the file to data set.
        /// </summary>
        /// <param name="fullFilePath">The full file path.</param>
        /// <returns></returns>
        public DataSet ImportFileToDataSet(string fullFilePath, string tableName)
        {
            OleDbConnection objConn = null;
            DataSet data = new DataSet();

            string strConn = string.Format(@"Provider=Microsoft.ACE.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES'", fullFilePath);//IMEX=1为只读
            //"Provider=Microsoft.Jet.OleDb.4.0;data source=c:\mm.xlsx;Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""";
            objConn = new OleDbConnection(strConn);
            objConn.Open();
            //System .Data . DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            //int SheetIndex = 0;
            //string tableName = schemaTable.Rows[SheetIndex][2].ToString().Trim();

            string strSql = string.Format("Select   *   From   [{0}$]", tableName);
            OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
            OleDbDataAdapter sqlada = new OleDbDataAdapter();
            sqlada.SelectCommand = objCmd;
            sqlada.Fill(data);
            objConn.Close();

            return data;
        }


        /// <summary>
        /// Gets the XLS first data table.
        /// </summary>
        /// <param name="fullFilePath">The full file path.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <returns></returns>
        public DataTable GetXlsFirstDataTable(string fullFilePath, string tableName)
        {
            return ImportFileToDataSet(fullFilePath, tableName).Tables[0];
        }
    }
}
